package com.typingtutor.dto;

import com.typingtutor.db.DatabaseManager;
import com.typingtutor.model.FieldKeyInfo;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

public class FieldKeyInfoDAO {
    private static volatile FieldKeyInfoDAO INSTANCE;
    private FieldKeyInfoDAO() {
        // 私有构造函数，防止实例化
    }
    public static FieldKeyInfoDAO getInstance() {
        if (INSTANCE == null) {
            synchronized (FieldKeyInfoDAO.class) {
                if (INSTANCE == null) {
                    INSTANCE = new FieldKeyInfoDAO();
                }
            }
        }
        return INSTANCE;
    }

    public List<FieldKeyInfo> findByFolderRandomRulesId(String folderRandomRulesId) {
        String selectSql = "SELECT fki.* " +
                "FROM folder_rule_field_key_relation frfkr " +
                "JOIN folder_random_rules frr ON frfkr.folder_random_rules_id = frr.id " +
                "JOIN field_key_info fki ON frfkr.field_key_info_id = fki.id " +
                "WHERE frr.id = ?";
        try (Connection connection = DatabaseManager.getConnection();
             PreparedStatement pstmt = connection.prepareStatement(selectSql)) {
            pstmt.setString(1, folderRandomRulesId);
            ResultSet rs = pstmt.executeQuery();
            List<FieldKeyInfo> fieldKeyInfos = new ArrayList<>();
            while (rs.next()) {
                fieldKeyInfos.add(new FieldKeyInfo(rs.getString("id"),
                        rs.getString("field_key"),
                        rs.getBoolean("display")));
            }
            return fieldKeyInfos;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    public List<FieldKeyInfo> findAllFieldKeyByFolderId(String folderId) {
        String selectSql = "SELECT fki.* " +
                "FROM field_key_info fki " +
                "JOIN folder_rule_field_key_relation frfkr ON fki.id = frfkr.field_key_info_id " +
                "JOIN folder_random_rules frr ON frfkr.folder_random_rules_id = frr.id " +
                "WHERE frr.folder_id = ?";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            pstmt.setString(1, folderId);
            ResultSet rs = pstmt.executeQuery();
            List<FieldKeyInfo> fieldKeyInfos = new ArrayList<>();
            while (rs.next()) {
                fieldKeyInfos.add(new FieldKeyInfo(rs.getString("id"),
                        rs.getString("field_key"),
                        rs.getBoolean("display")));
            }
            return fieldKeyInfos;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.emptyList();
    }

    public List<FieldKeyInfo> findFieldKeyInfoByFolderRandomRulesId(String folderRandomRulesId) {
        List<FieldKeyInfo> fieldKeyInfos = new ArrayList<>();
        String selectSql = "SELECT fki.id, fki.field_key, fki.display " +
                "FROM field_key_info fki " +
                "JOIN folder_rule_field_key_relation frfkr ON fki.id = frfkr.field_key_info_id " +
                "JOIN folder_random_rules frr ON frfkr.folder_random_rules_id = frr.id " +
                "WHERE frr.id = ? AND fki.display = true";

        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            pstmt.setString(1, folderRandomRulesId);
            ResultSet rs = pstmt.executeQuery();

            while (rs.next()) {
                String id = rs.getString("id");
                String fieldKey = rs.getString("field_key");
                boolean display = rs.getBoolean("display");
                FieldKeyInfo fieldKeyInfo = new FieldKeyInfo(id, fieldKey, display);
                fieldKeyInfos.add(fieldKeyInfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return fieldKeyInfos;
    }

    public List<FieldKeyInfo> findFieldKeyInfoByRandomRuleId(String randomRuleId) {
        String selectSql = "SELECT fki.* " +
                "FROM field_key_info fki " +
                "JOIN folder_rule_field_key_relation frfkr ON fki.id = frfkr.field_key_info_id " +
                "JOIN folder_random_rules frr ON frfkr.folder_random_rules_id = frr.id " +
                "WHERE frr.id = ?; ";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            pstmt.setString(1, randomRuleId);
            ResultSet query = pstmt.executeQuery();
            List<FieldKeyInfo> fieldKeyInfos = new ArrayList<>();
            while (query.next()) {
                fieldKeyInfos.add(new FieldKeyInfo(query.getString("id")
                        , query.getString("field_key")
                , query.getBoolean("display")));
            }
            return fieldKeyInfos;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return Collections.EMPTY_LIST;
    }

    public FieldKeyInfo findAllFieldById(String id) {
        String selectSql = "SELECT * FROM field_key_info WHERE id = ?";
        try (Connection connection = DatabaseManager.getConnection()) {
            PreparedStatement pstmt = connection.prepareStatement(selectSql);
            pstmt.setString(1, id);
            ResultSet rs = pstmt.executeQuery();
            FieldKeyInfo fieldMapping = null;
            while (rs.next()) {
               fieldMapping = new FieldKeyInfo(rs.getString("id")
                , rs.getString("field_key")
                , rs.getBoolean("display"));
            }
            return fieldMapping;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
